﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Services;
using System.Data;
using LdExpress;
using System.Linq;
using System.Web.UI;

/// <summary>
/// Summary description for AutoComplete
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class AutoComplete : System.Web.Services.WebService
{

    public AutoComplete()
    {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }
    #region Goods Data
    [WebMethod]
    public string[] GoodsAutoCompleteAjaxRequest(string prefixText, int count)
    {
        List<string> ajaxDataCollection = new List<string>();
        DataTable _objdt = new DataTable();
        _objdt = GetGoodsDataFromDataBase(prefixText);
        if (_objdt.Rows.Count > 0)
        {
            for (int i = 0; i < _objdt.Rows.Count; i++)
            {
                //ajaxDataCollection.Add(_objdt.Rows[i]["Code"].ToString() + ": " + _objdt.Rows[i]["Name"].ToString());

                string item = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem
                  (_objdt.Rows[i]["Name"].ToString(),
                  _objdt.Rows[i]["Code"].ToString());
                ajaxDataCollection.Add(item);
            }
        }
        return ajaxDataCollection.ToArray();
    }
    public DataTable GetGoodsDataFromDataBase(string prefixText)
    {
        string sqlCommand = "select * from tbl_Goods where Name like '%" + prefixText + "%' OR Code like '%" + prefixText + "%';";
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        return ds.Tables[0];
    }
    #endregion

    #region Country
    [WebMethod]
    public string[] CountryAutoCompleteAjaxRequest(string prefixText, int count)
    {
        List<string> ajaxDataCollection = new List<string>();
        DataTable _objdt = new DataTable();
        _objdt = GetCountryDataFromDataBase(prefixText);
        if (_objdt.Rows.Count > 0)
        {
            for (int i = 0; i < _objdt.Rows.Count; i++)
            {
                string item = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem
                 (_objdt.Rows[i]["CountryName"].ToString(),
                 _objdt.Rows[i]["CountryCode"].ToString());
                ajaxDataCollection.Add(item);
            }
        }
        return ajaxDataCollection.ToArray();
    }
    public DataTable GetCountryDataFromDataBase(string prefixText)
    {
        string sqlCommand = "select CountryName,CountryCode from tbl_countries where CountryName like '%" + prefixText + "%'";
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        return ds.Tables[0];
    }
    #endregion

    #region District
    [WebMethod]
    public string[] DistrictAutoCompleteAjaxRequest(string prefixText, int count)
    {
        List<string> ajaxDataCollection = new List<string>();
        DataTable _objdt = new DataTable();
        _objdt = GetDistrictDataFromDataBase(prefixText);
        if (_objdt.Rows.Count > 0)
        {
            for (int i = 0; i < _objdt.Rows.Count; i++)
            {
                string item = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem
                 (_objdt.Rows[i]["Name"].ToString(),
                 _objdt.Rows[i]["ID"].ToString());
                ajaxDataCollection.Add(item);
            }
        }
        return ajaxDataCollection.ToArray();
    }
    public DataTable GetDistrictDataFromDataBase(string prefixText)
    {
        string sqlCommand = "SELECT tbl_Huyen.ID,CONCAT(tbl_Huyen.Ten,', ',TinhTable.Ten) as Name " +
            " FROM tbl_Huyen LEFT JOIN tbl_Tinh TinhTable on tbl_Huyen.TinhID = TinhTable.ID" +
            " WHERE tbl_Huyen.Ten like '%" + prefixText + "%'";
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        return ds.Tables[0];
    }
    #endregion

    #region Location
    [WebMethod]
    public string[] LocationAutoCompleteAjaxRequest(string prefixText, int count)
    {
        List<string> ajaxDataCollection = new List<string>();
        DataTable _objdt = new DataTable();
        _objdt = GetLocationDataFromDataBase(prefixText);
        if (_objdt.Rows.Count > 0)
        {
            for (int i = 0; i < _objdt.Rows.Count; i++)
            {
                string item = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem
                 (_objdt.Rows[i]["Name"].ToString(),
                 _objdt.Rows[i]["ID"].ToString());
                ajaxDataCollection.Add(item);
            }
        }
        return ajaxDataCollection.ToArray();
    }
    public DataTable GetLocationDataFromDataBase(string prefixText)
    {
        string sqlCommand = "select ID,Name from tbl_Location where Name like '%" + prefixText + "%'";
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        return ds.Tables[0];
    }
    #endregion

    #region Region
    [WebMethod]
    public string[] RegionAutoCompleteAjaxRequest(string prefixText, int count)
    {
        List<string> ajaxDataCollection = new List<string>();
        DataTable _objdt = new DataTable();
        _objdt = GetRegionDataFromDataBase(prefixText);
        if (_objdt.Rows.Count > 0)
        {
            for (int i = 0; i < _objdt.Rows.Count; i++)
            {
                string item = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem
                (_objdt.Rows[i]["Name"].ToString(),
                _objdt.Rows[i]["ID"].ToString());
                ajaxDataCollection.Add(item);
            }
        }
        return ajaxDataCollection.ToArray();
    }
    public DataTable GetRegionDataFromDataBase(string prefixText)
    {
        string sqlCommand = "select * from tbl_Region where Name like '%" + prefixText + "%'";
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        return ds.Tables[0];
    }
    #endregion

    #region Service Area
    [WebMethod]
    public string[] ServiceAreaAutoCompleteAjaxRequest(string prefixText, int count)
    {
        List<string> ajaxDataCollection = new List<string>();
        DataTable _objdt = new DataTable();
        _objdt = GetServiceAreaDataFromDataBase(prefixText);
        if (_objdt.Rows.Count > 0)
        {
            for (int i = 0; i < _objdt.Rows.Count; i++)
            {
                string item = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem
                 (_objdt.Rows[i]["Code"].ToString(),
                 _objdt.Rows[i]["Code"].ToString());
                ajaxDataCollection.Add(item);
            }
        }
        return ajaxDataCollection.ToArray();
    }
    public DataTable GetServiceAreaDataFromDataBase(string prefixText)
    {
        string sqlCommand = "select Code,Name from tbl_ServiceAreas where Code like '%" + prefixText + "%'";
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        return ds.Tables[0];
    }
    #endregion

    #region Port
    [WebMethod]
    public string[] PortAutoCompleteAjaxRequest(string prefixText, int count)
    {
        List<string> ajaxDataCollection = new List<string>();
        DataTable _objdt = new DataTable();
        _objdt = GetPortDataFromDataBase(prefixText);
        if (_objdt.Rows.Count > 0)
        {
            for (int i = 0; i < _objdt.Rows.Count; i++)
            {
                string item = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem
                 (_objdt.Rows[i]["Name"].ToString(),
                 _objdt.Rows[i]["ID"].ToString());
                ajaxDataCollection.Add(item);
            }
        }
        return ajaxDataCollection.ToArray();
    }
    public DataTable GetPortDataFromDataBase(string prefixText)
    {
        string sqlCommand = "select ID,Name from tbl_Ports where Name like '%" + prefixText + "%'";
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        return ds.Tables[0];
    }
    #endregion
    #region Port to Charges
    [WebMethod]
    public string[] PortChargeAutoCompleteAjaxRequest(string prefixText, int count)
    {
        List<string> ajaxDataCollection = new List<string>();
        DataTable _objdt = new DataTable();
        _objdt = GetPortChargeDataFromDataBase(prefixText);
        if (_objdt.Rows.Count > 0)
        {
            for (int i = 0; i < _objdt.Rows.Count; i++)
            {
                string item = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem
                 (_objdt.Rows[i]["Name"].ToString(),
                 _objdt.Rows[i]["ID"].ToString());
                ajaxDataCollection.Add(item);
            }
        }
        return ajaxDataCollection.ToArray();
    }
    public DataTable GetPortChargeDataFromDataBase(string prefixText)
    {
        string sqlCommand = "select ID,Name from tbl_Ports where Name like '%" + prefixText + "%' AND IsLocalUsed=1";
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        return ds.Tables[0];
    }
    #endregion

    #region Carrs
    [WebMethod]
    public string[] CarrsAutoCompleteAjaxRequest(string prefixText, int count)
    {
        List<string> ajaxDataCollection = new List<string>();
        DataTable _objdt = new DataTable();
        _objdt = GetCarrsDataFromDataBase(prefixText);
        if (_objdt.Rows.Count > 0)
        {
            for (int i = 0; i < _objdt.Rows.Count; i++)
            {
                string item = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem
                 (_objdt.Rows[i]["Name"].ToString(),
                 _objdt.Rows[i]["ID"].ToString());
                ajaxDataCollection.Add(item);
            }
        }
        return ajaxDataCollection.ToArray();
    }
    public DataTable GetCarrsDataFromDataBase(string prefixText)
    {
        string sqlCommand = "select ID,Name from tbl_carrs where Name like '%" + prefixText + "%'";
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        return ds.Tables[0];
    }
    #endregion
    #region Carrs
    [WebMethod]
    public string[] TruckerAutoCompleteAjaxRequest(string prefixText, int count)
    {
        List<string> ajaxDataCollection = new List<string>();
        DataTable _objdt = new DataTable();
        _objdt = GetTruckerDataFromDataBase(prefixText);
        if (_objdt.Rows.Count > 0)
        {
            for (int i = 0; i < _objdt.Rows.Count; i++)
            {
                string item = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem
                 (_objdt.Rows[i]["BizName"].ToString(),
                 _objdt.Rows[i]["ID"].ToString());
                ajaxDataCollection.Add(item);
            }
        }
        return ajaxDataCollection.ToArray();
    }
    public DataTable GetTruckerDataFromDataBase(string prefixText)
    {
        string sqlCommand = "select ID,BizName from tbl_Trucker where BizName like '%" + prefixText + "%'";
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        return ds.Tables[0];
    }
    #endregion

    #region Khach hang Cua User Logined
    [WebMethod(EnableSession = true)]
    public string[] CustomerOfUserAutoCompleteAjaxRequest(string prefixText, int count)
    {
        List<string> ajaxDataCollection = new List<string>();
        DataTable _objdt = new DataTable();
        _objdt = GetCustOfUserDataFromDataBase(prefixText);
        if (_objdt.Rows.Count > 0)
        {
            for (int i = 0; i < _objdt.Rows.Count; i++)
            {
                string item = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem
                 (_objdt.Rows[i]["Name"].ToString(),
                 _objdt.Rows[i]["ID"].ToString());
                ajaxDataCollection.Add(item);
            }
        }
        return ajaxDataCollection.ToArray();
    }
    public DataTable GetCustOfUserDataFromDataBase(string prefixText)
    {
        string sqlCommand = "select ID,Name from tbl_CustomerRelation where Name like '%" + prefixText + "%' AND UserID=" + Session["logined_id"].ToString();
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        return ds.Tables[0];
    }
    #endregion

}
